/*
 * Copyright (c) 2018 maoyan.com
 * All rights reserved.
 *
 */
package com.darren.euc.service.impl;

import com.darren.euc.dao.CommonDao;
import com.darren.euc.dao.MsgDao;
import com.darren.euc.dao.ReadedMsgDao;
import com.darren.euc.dao.SpaceDao;
import com.darren.euc.entity.*;
import com.darren.euc.service.MsgService;
import com.darren.euc.util.Kv;
import com.darren.euc.util.StringUtil;
import com.google.common.collect.Lists;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 在这里编写类的功能描述
 *
 * @author fangwenhao
 * @created 2018/3/11
 */
@Service
public class MsgServiceImpl implements MsgService {

    @Autowired
    private MsgDao msgDao;
    @Autowired
    private ReadedMsgDao readedMsgDao;

    @Autowired
    private CommonDao commonDao;

    @Autowired
    private SpaceDao spaceDao;

    @Override
    public PageBean queryForPageMsgHQL(JSONObject data, Integer pageSize, int currentPage) {
        String title = data.getString("title");
        Long userAreaId = data.getLong("userAreaId");// 0 表示所有 x 某个空间
        Object userTypeJson = data.get("userType");
        String hql = "from Msg where isDeleted = 0 ";
        Map<String, Object> map = new HashMap<>();
        Integer userType;
        if (!userTypeJson.equals("null")){
            userType = data.getInt("userType");//目标用户类型 -1 特定会员 0 全部会员 x 某种管理员 -2 用户分组
            hql +="and find_in_set(:userType, userArea) > 0 ";
            map.put("userType",userType);
        }
        if (!StringUtil.isEmptyOrWhitespace(title)){
            hql += "and title like :title ";
            map.put("title","%"+title+"%");
        }
        if (userAreaId != null && userAreaId > 0){
            hql += "and userAreaId in :userAreaId ";
            map.put("userAreaId", Lists.newArrayList(userAreaId,0l));
        }
        Pageable pageable = new PageRequest(currentPage-1, pageSize);
        List<Msg> mapList = commonDao.getResultPage(hql+" order by id desc ",map,pageable);
        for (Msg msg : mapList){
            modifyMsg(msg);
        }
        Integer total = Long.valueOf(commonDao.getCount(hql,map)).intValue();
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(pageSize);
        pageBean.setCurrentPage(currentPage);
        pageBean.setTotalRow(total);
        pageBean.setTotalPage(total/pageSize + 1);
        pageBean.setList(mapList);
        pageBean.init();
        return pageBean;
    }

    @Override
    public Msg save(Msg msg) {
        return msgDao.save(msg);
    }

    @Override
    public Msg findById(Long id) {
        Msg msg = msgDao.findById(id);
        modifyMsg(msg);
        return msg;
    }

    @Override
    public Msg delete(Long id) {
        Msg msg = msgDao.findById(id);
        msg.setIsDeleted(1);
        return msgDao.update(msg);
    }

    /**
     * 完整SQL
     SELECT m.id message_id,m.title,m.content,m.add_time time
        ,IF((SELECT id FROM euc_readed_msg r WHERE r.member_id=1 AND r.msg_id=m.id),1,0) had_read
     FROM euc_message m
     WHERE m.tagret_type=1
         OR (find_in_set(1, REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=2)
         OR (find_in_set(1, REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=4)
     */
    @Override
    public PageBean msgList(Map map, int pn, int ps) {
        String select = "SELECT m.id `message_id`,m.`title`,m.`content`,m.`add_time` 'time'";

        if(map.get("member_id") != null) {//有用户id，查询阅读状态
            select += ",IF((SELECT id FROM euc_readed_msg r WHERE r.member_id="+map.get("member_id")+" AND r.msg_id=m.id),1,0) had_read";
        }

        String from = " FROM euc_message m WHERE m.tagret_type=1";
        if(map.get("mobile") != null) {//有手机号，查询发送给手机号的消息
            from += " OR (find_in_set("+map.get("mobile")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=3)";
        }
        if(map.get("space_id") != null) {//有空间id，查询发送给对应空间的消息
            from += " OR (find_in_set("+map.get("space_id")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=4)";
        }
        //按照Id排序
        from += " order by id desc ";
        String limit =  " limit "+(pn-1)*ps + ","+ ps;
        List<Map> list = msgDao.getDataMapList(select + from + limit);
        list.forEach(x->{//数据处理
            x.put("time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(((Timestamp) x.get("time")).getTime()));
            x.put("digest", delHTMLTag((String) x.getOrDefault("content", "")));
            x.put("had_read", x.get("had_read") == null ? 1 : x.get("had_read"));
            x.put("detailUrl", "/_msg/msg_detail?message_id="+x.get("message_id"));
        });

        PageBean pageBean = msgDao.queryForPageBySQL("SELECT * " + from, ps, pn);
        pageBean.setList(list);
        return pageBean;
    }


    @Override
    public PageBean msgListV2(Map map, int pn, int ps) {
        String select = "SELECT m.id `message_id`,m.`title`,m.`content`,m.`add_time` 'time'";

        if(map.get("member_id") != null) {//有用户id，查询阅读状态
            select += ",IF((SELECT id FROM euc_readed_msg r WHERE r.member_id="+map.get("member_id")+" AND r.msg_id=m.id),1,0) had_read";
        }
        String from = " FROM euc_message m WHERE 1=1";
        if (map.get("space_id") != null){
            from += " AND m.user_area_id in (0," + map.get("space_id") +")";
        }

        from += " AND m.tagret_type = 1";
        if(map.get("mobile") != null) {//有手机号，查询发送给手机号的消息
            from += " OR (find_in_set("+map.get("mobile")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=3)";
        }

        if(map.get("space_id") != null && map.get("client_id") != null) {// 空间id 与 客户id 都不为空
            from += " OR ( m.user_area_id in (0,"+ map.get("space_id") +") AND m.tagret_info in (0,"+ map.get("client_id") +") AND m.tagret_type=4)";
        }

        //按照Id排序
        from += " order by id desc ";
        String limit =  " limit "+(pn-1)*ps + ","+ ps;
        List<Map> list = msgDao.getDataMapList(select + from + limit);
        list.forEach(x->{//数据处理
            x.put("time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(((Timestamp) x.get("time")).getTime()));
            x.put("had_read", x.get("had_read") == null ? 1 : x.get("had_read"));
            x.put("detailUrl", "/_msg/msg_detail?message_id="+x.get("message_id"));
            try {
            	String con = (String) x.get("content");
            	String con2 = URLDecoder.decode(con,(String)"UTF-8");
            	
            	String regEx_script="<script[^>]*?>[\\s\\S]*?<\\/script>"; //定义script的正则表达式 
                String regEx_style="<style[^>]*?>[\\s\\S]*?<\\/style>"; //定义style的正则表达式 
                String regEx_html="<[^>]+>"; //定义HTML标签的正则表达式 
                 
                Pattern p_script=Pattern.compile(regEx_script,Pattern.CASE_INSENSITIVE); 
                Matcher m_script=p_script.matcher(con2); 
                con2=m_script.replaceAll(""); //过滤script标签 
                 
                Pattern p_style=Pattern.compile(regEx_style,Pattern.CASE_INSENSITIVE); 
                Matcher m_style=p_style.matcher(con2); 
                con2=m_style.replaceAll(""); //过滤style标签 
                 
                Pattern p_html=Pattern.compile(regEx_html,Pattern.CASE_INSENSITIVE); 
                Matcher m_html=p_html.matcher(con2); 
                con2=m_html.replaceAll(""); //过滤html标签 

                con2 = con2.trim(); //返回文本字符串 
            	
				x.put("content", con2);
				x.put("digest", con2);
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        });

        PageBean pageBean = msgDao.queryForPageBySQL("SELECT * " + from, ps, pn);
        pageBean.setList(list);
        return pageBean;
    }

    @Override
    public PageBean msgListForWeb(User user, int pn, int ps) {
        String select = "SELECT m.id `message_id`,m.`title`,m.`content` content,m.`add_time` 'time'";
        select += ",IF((SELECT r.id FROM euc_readed_msg r WHERE r.member_id="+user.getId()+" AND r.msg_id=m.id),1,0) had_read";
        List<Integer> roleIds = getRolesByUser(user);
        String from = " FROM euc_message m WHERE m.tagret_type=2 AND (1>1";
        for (Integer roleId : roleIds){
            from += " OR find_in_set("+roleId+",m.user_area)";
        }
//        from += " OR find_in_set("+2+",m.user_area)";
        from += ")";
        from += " order by m.id desc ";
        String limit =  " limit "+(pn-1)*ps + ","+ ps;
        List<Map> list = msgDao.getDataMapList(select + from + limit);
        list.forEach(x->{//数据处理
            x.put("time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(((Timestamp) x.get("time")).getTime()));
            x.put("digest", delHTMLTag((String) x.getOrDefault("content", "")));
            x.put("had_read", x.get("had_read") == null ? 1 : x.get("had_read"));
            x.put("message_id", x.get("message_id"));
        });

        PageBean pageBean = msgDao.queryForPageBySQL("SELECT * " + from, ps, pn);
        pageBean.setList(list);
        return pageBean;
    }

    @Override
    public Map msgInfo(int msg_id) {
        String sql = "SELECT m.id message_id,m.title,m.content,m.add_time time " +
                "FROM euc_message m WHERE m.id=?";
        return msgDao.getDataMap(sql, msg_id);
    }

    @Override
    public Map msgCount(Kv kv) {
        String sql = "SELECT COUNT(n.message_id) count FROM (" +
                "  SELECT m.id message_id,m.title,m.content,m.add_time time";
        if(kv.get("member_id") != null) {//有用户id，查询阅读状态
            sql += ",IF((SELECT id FROM euc_readed_msg r WHERE r.member_id="+kv.get("member_id")+" AND r.msg_id=m.id),1,0) had_read";
        }
        sql += "  FROM euc_message m WHERE m.tagret_type=1";
        if(kv.get("mobile") != null) {//有手机号，查询发送给手机号的消息
            sql += " OR (find_in_set("+kv.get("mobile")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=3)";
        }
        if(kv.get("space_id") != null) {//有空间id，查询发送给对应空间的消息
            sql += " OR (find_in_set("+kv.get("space_id")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=4)";
        }
        sql += ") n WHERE n.had_read=0";

        return msgDao.getDataMap(sql);
    }

    @Override
    public Map msgCountV2(Kv kv) {
        String sql = "SELECT COUNT(n.message_id) count FROM (" +
                "  SELECT m.id message_id,m.title,m.content,m.add_time time";
        if(kv.get("member_id") != null) {//有用户id，查询阅读状态
            sql += ",IF((SELECT id FROM euc_readed_msg r WHERE r.is_deleted = 0 AND r.member_id="+kv.get("member_id")+" AND r.msg_id=m.id),1,0) had_read";
        }
        sql += "  FROM euc_message m WHERE 1=1";
        if (kv.get("space_id") != null){
            sql += " AND m.user_area_id in (0," + kv.get("space_id") +")";
        }
        sql += " AND m.tagret_type=1";
        if(kv.get("mobile") != null) {//有手机号，查询发送给手机号的消息
            sql += " OR (find_in_set("+kv.get("mobile")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=3)";
        }
        if(kv.get("space_id") != null && kv.get("client_id") != null) {// 空间id 与 客户id 都不为空
            sql += " OR ( m.user_area_id in (0,"+ kv.get("space_id") +") AND m.tagret_info in (0,"+ kv.get("client_id") +") AND m.tagret_type=4)";
        }
        sql += ") n WHERE n.had_read=0";

        return msgDao.getDataMap(sql);
    }

    @Override
    public Map msgCountForWeb(Kv kv) {
        String sql = "SELECT COUNT(n.message_id) count FROM (" +
                "  SELECT m.id message_id,m.title,m.content,m.add_time time";
        User user = (User) kv.get("user");
        if(kv.get("user") != null) {//有用户id，查询阅读状态
            sql += ",IF((SELECT id FROM euc_readed_msg r WHERE r.is_deleted = 1 AND r.member_id="+user.getId()+" AND r.msg_id=m.id),1,0) had_read";
        }
        List<Integer> roleIds = getRolesByUser(user);
        sql += " FROM euc_message m WHERE m.tagret_type=2 AND (1>1";
        for (Integer roleId : roleIds){
            sql += " OR find_in_set("+roleId+",m.user_area)";
        }
//        sql += " OR find_in_set("+2+",m.user_area)";
        sql += ")) n WHERE n.had_read=0 ";
        return msgDao.getDataMap(sql);
    }

    @Override
    public void readedAll(Kv kv) {
        String sql = "SELECT n.message_id mid FROM (" +
                "  SELECT m.id message_id,m.title,m.content,m.add_time time";
        if(kv.get("member_id") != null) {//有用户id，查询阅读状态
            sql += ",IF((SELECT id FROM euc_readed_msg r WHERE r.member_id="+kv.get("member_id")+" AND r.msg_id=m.id),1,0) had_read";
        }
        sql += "  FROM euc_message m WHERE m.tagret_type=1";
        if(kv.get("mobile") != null) {//有手机号，查询发送给手机号的消息
            sql += " OR (find_in_set("+kv.get("mobile")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=3)";
        }
        if(kv.get("space_id") != null) {//有空间id，查询发送给对应空间的消息
            sql += " OR (find_in_set("+kv.get("space_id")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=4)";
        }
        sql += ") n WHERE n.had_read=0";

        JSONArray jsonArray = JSONArray.fromObject(msgDao.getDataMapList(sql));


        for (Object obj : jsonArray){
            ReadedMsg readedMsg = new ReadedMsg();
            JSONObject jsonObject = (JSONObject)obj;
            readedMsg.setMsgId(jsonObject.getLong("mid"));
            readedMsg.setMemberId((Integer) kv.get("member_id"));
            readedMsg.setAddTime(new Date());
            readedMsg.setModTime(new Date());
            readedMsg.setIsDeleted(0);
            readedMsgDao.save(readedMsg);
        }
    }

    @Override
    public void readedAllV2(Kv kv) {
        String sql = "SELECT n.message_id mid FROM (" +
                "  SELECT m.id message_id,m.title,m.content,m.add_time time";
        if(kv.get("member_id") != null) {//有用户id，查询阅读状态
            sql += ",IF((SELECT id FROM euc_readed_msg r WHERE r.is_deleted = 0 AND r.member_id="+kv.get("member_id")+" AND r.msg_id=m.id),1,0) had_read";
        }
        sql += " FROM euc_message m WHERE 1=1";
        if (kv.get("space_id") != null){
            sql += " AND m.user_area_id in (0," + kv.get("space_id") +")";
        }
        sql += " AND m.tagret_type=1";
        if(kv.get("mobile") != null) {//有手机号，查询发送给手机号的消息
            sql += " OR (find_in_set("+kv.get("mobile")+", REPLACE(m.tagret_info, '|', ',')) AND m.tagret_type=3)";
        }
        if(kv.get("space_id") != null && kv.get("client_id") != null) {// 空间id 与 客户id 都不为空
            sql += " OR ( m.user_area_id in (0,"+ kv.get("space_id") +") AND m.tagret_info in (0,"+ kv.get("client_id") +") AND m.tagret_type=4)";
        }
        sql += ") n WHERE n.had_read=0";

        JSONArray jsonArray = JSONArray.fromObject(msgDao.getDataMapList(sql));

        for (Object obj : jsonArray){
            ReadedMsg readedMsg = new ReadedMsg();
            JSONObject jsonObject = (JSONObject)obj;
            readedMsg.setMsgId(jsonObject.getLong("mid"));
            readedMsg.setMemberId((Integer) kv.get("member_id"));
            readedMsg.setAddTime(new Date());
            readedMsg.setModTime(new Date());
            readedMsg.setIsDeleted(0);
            readedMsgDao.save(readedMsg);
        }
    }

    @Override
    public void readedAllForWeb(Kv kv) {
        String sql = "SELECT n.message_id mid FROM (" +
                "  SELECT m.id message_id,m.title,m.content,m.add_time time";
        User user = (User) kv.get("user");
        if(kv.get("user") != null) {//有用户id，查询阅读状态
            sql += ",IF((SELECT id FROM euc_readed_msg r WHERE r.is_deleted = 1 AND r.member_id="+user.getId()+" AND r.msg_id=m.id),1,0) had_read";
        }
        List<Integer> roleIds = getRolesByUser(user);
        sql += " FROM euc_message m WHERE m.tagret_type=2 AND (1>1";
        for (Integer roleId : roleIds){
            sql += " OR find_in_set("+roleId+",m.user_area)";
        }
//        sql += " OR find_in_set("+2+",m.user_area)";
        sql += ")) n WHERE n.had_read=0 ";
        JSONArray jsonArray = JSONArray.fromObject(msgDao.getDataMapList(sql));
        for (Object obj : jsonArray){
            ReadedMsg readedMsg = new ReadedMsg();
            JSONObject jsonObject = (JSONObject)obj;
            readedMsg.setMsgId(jsonObject.getLong("mid"));
            readedMsg.setMemberId(user.getId());
            readedMsg.setAddTime(new Date());
            readedMsg.setModTime(new Date());
            readedMsg.setIsDeleted(1);
            readedMsgDao.save(readedMsg);
        }
    }

    @Override
    public void msgLogSave(int member_id, int message_id) {
        Map dataMap = msgDao.getDataMap("SELECT * FROM euc_readed_msg r WHERE r.msg_id=? AND r.member_id=?", message_id, member_id);
        if (dataMap == null) {
            ReadedMsg readedMsg = new ReadedMsg();
            readedMsg.setAddTime(new Date());
            readedMsg.setModTime(new Date());
            readedMsg.setMemberId(member_id);
            readedMsg.setMsgId((long) message_id);
            readedMsg.setIsDeleted(0);
            readedMsgDao.save(readedMsg);
        }
    }

    @Override
    public void msgLogSaveForWeb(int user_id, int message_id) {
        Map dataMap = msgDao.getDataMap("SELECT * FROM euc_readed_msg r WHERE r.msg_id=? AND r.member_id=?", message_id, user_id);
        if (dataMap == null) {
            ReadedMsg readedMsg = new ReadedMsg();
            readedMsg.setAddTime(new Date());
            readedMsg.setModTime(new Date());
            readedMsg.setMemberId(user_id);
            readedMsg.setMsgId((long) message_id);
            readedMsg.setIsDeleted(1);
            readedMsgDao.save(readedMsg);
        }
    }

    private static String delHTMLTag(String htmlStr){
        String regEx_script="<script[^>]*?>[\\s\\S]*?<\\/script>"; //定义script的正则表达式
        String regEx_style="<style[^>]*?>[\\s\\S]*?<\\/style>"; //定义style的正则表达式
        String regEx_html="<[^>]+>"; //定义HTML标签的正则表达式

        Pattern p_script=Pattern.compile(regEx_script,Pattern.CASE_INSENSITIVE);
        Matcher m_script=p_script.matcher(htmlStr);
        htmlStr=m_script.replaceAll(""); //过滤script标签

        Pattern p_style=Pattern.compile(regEx_style,Pattern.CASE_INSENSITIVE);
        Matcher m_style=p_style.matcher(htmlStr);
        htmlStr=m_style.replaceAll(""); //过滤style标签

        Pattern p_html=Pattern.compile(regEx_html,Pattern.CASE_INSENSITIVE);
        Matcher m_html=p_html.matcher(htmlStr);
        htmlStr=m_html.replaceAll(""); //过滤html标签

        return htmlStr.trim(); //返回文本字符串
    }

    private void modifyMsg(Msg msg){
        if (msg.getUserAreaId() == 0){
            msg.setUserArea("全部");
        }else {
            Space space = spaceDao.findById(msg.getUserAreaId().intValue());
            if (space != null){
                msg.setUserArea(space.getSpaceName());
            }else {
                msg.setUserArea(null);
            }
        }
    }

    private List<Integer> getRolesByUser(User user){
        String sql = "SELECT role.id roleId " +
                "from euc_roles as role " +
                "INNER JOIN euc_user_role as userrole on userrole.role_id = role.id " +
                "INNER JOIN euc_users as users on users.user_id = userrole.user_id " +
                "where users.user_id = " + user.getId();
        List<Map> list = msgDao.getDataMapList(sql);
        if (CollectionUtils.isEmpty(list)){
            return Collections.emptyList();
        }
        return list.stream().map(item->(Integer)item.get("roleId")).collect(Collectors.toList());
    }

}
